#!/usr/bin/env python
#
# Copyright (C) 2009 Google Inc.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.


"""Contains a client to communicate with the Google Spreadsheets servers.

For documentation on the Spreadsheets API, see:
http://code.google.com/apis/spreadsheets/
"""


__author__ = 'j.s@google.com (Jeff Scudder)'


import gdata.client
import gdata.gauth
import gdata.spreadsheets.data
import atom.data
import atom.http_core


SPREADSHEETS_URL = ('https://spreadsheets.google.com/feeds/spreadsheets'
                    '/private/full')
WORKSHEETS_URL = ('https://spreadsheets.google.com/feeds/worksheets/'
                  '%s/private/full')
WORKSHEET_URL = ('https://spreadsheets.google.com/feeds/worksheets/'
                 '%s/private/full/%s')
TABLES_URL = 'https://spreadsheets.google.com/feeds/%s/tables'
RECORDS_URL = 'https://spreadsheets.google.com/feeds/%s/records/%s'
RECORD_URL = 'https://spreadsheets.google.com/feeds/%s/records/%s/%s'
CELLS_URL = 'https://spreadsheets.google.com/feeds/cells/%s/%s/private/full'
CELL_URL = ('https://spreadsheets.google.com/feeds/cells/%s/%s/private/full/'
            'R%sC%s')
LISTS_URL = 'https://spreadsheets.google.com/feeds/list/%s/%s/private/full'


class SpreadsheetsClient(gdata.client.GDClient):
    api_version = '3'
    auth_service = 'wise'
    auth_scopes = gdata.gauth.AUTH_SCOPES['wise']
    ssl = True

    def get_spreadsheets(self, auth_token=None,
                         desired_class=gdata.spreadsheets.data.SpreadsheetsFeed,
                         **kwargs):
        """Obtains a feed with the spreadsheets belonging to the current user.

        Args:
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
          desired_class: class descended from atom.core.XmlElement to which a
                         successful response should be converted. If there is no
                         converter function specified (converter=None) then the
                         desired_class will be used in calling the
                         atom.core.parse function. If neither
                         the desired_class nor the converter is specified, an
                         HTTP reponse object will be returned. Defaults to
                         gdata.spreadsheets.data.SpreadsheetsFeed.
        """
        return self.get_feed(SPREADSHEETS_URL, auth_token=auth_token,
                             desired_class=desired_class, **kwargs)

    GetSpreadsheets = get_spreadsheets

    def get_worksheets(self, spreadsheet_key, auth_token=None,
                       desired_class=gdata.spreadsheets.data.WorksheetsFeed,
                       **kwargs):
        """Finds the worksheets within a given spreadsheet.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
          desired_class: class descended from atom.core.XmlElement to which a
                         successful response should be converted. If there is no
                         converter function specified (converter=None) then the
                         desired_class will be used in calling the
                         atom.core.parse function. If neither
                         the desired_class nor the converter is specified, an
                         HTTP reponse object will be returned. Defaults to
                         gdata.spreadsheets.data.WorksheetsFeed.
        """
        return self.get_feed(WORKSHEETS_URL % spreadsheet_key,
                             auth_token=auth_token, desired_class=desired_class,
                             **kwargs)

    GetWorksheets = get_worksheets

    def add_worksheet(self, spreadsheet_key, title, rows, cols,
                      auth_token=None, **kwargs):
        """Creates a new worksheet entry in the spreadsheet.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          title: str, The title to be used in for the worksheet.
          rows: str or int, The number of rows this worksheet should start with.
          cols: str or int, The number of columns this worksheet should start with.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
        """
        new_worksheet = gdata.spreadsheets.data.WorksheetEntry(
            title=atom.data.Title(text=title),
            row_count=gdata.spreadsheets.data.RowCount(text=str(rows)),
            col_count=gdata.spreadsheets.data.ColCount(text=str(cols)))
        return self.post(new_worksheet, WORKSHEETS_URL % spreadsheet_key,
                         auth_token=auth_token, **kwargs)

    AddWorksheet = add_worksheet

    def get_worksheet(self, spreadsheet_key, worksheet_id,
                      desired_class=gdata.spreadsheets.data.WorksheetEntry,
                      auth_token=None, **kwargs):
        """Retrieves a single worksheet.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          worksheet_id: str, The unique ID for the worksheet withing the desired
                        spreadsheet.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
          desired_class: class descended from atom.core.XmlElement to which a
                         successful response should be converted. If there is no
                         converter function specified (converter=None) then the
                         desired_class will be used in calling the
                         atom.core.parse function. If neither
                         the desired_class nor the converter is specified, an
                         HTTP reponse object will be returned. Defaults to
                         gdata.spreadsheets.data.WorksheetEntry.

        """
        return self.get_entry(WORKSHEET_URL % (spreadsheet_key, worksheet_id,),
                              auth_token=auth_token, desired_class=desired_class,
                              **kwargs)

    GetWorksheet = get_worksheet

    def add_table(self, spreadsheet_key, title, summary, worksheet_name,
                  header_row, num_rows, start_row, insertion_mode,
                  column_headers, auth_token=None, **kwargs):
        """Creates a new table within the worksheet.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          title: str, The title for the new table within a worksheet.
          summary: str, A description of the table.
          worksheet_name: str The name of the worksheet in which this table
                          should live.
          header_row: int or str, The number of the row in the worksheet which
                      will contain the column names for the data in this table.
          num_rows: int or str, The number of adjacent rows in this table.
          start_row: int or str, The number of the row at which the data begins.
          insertion_mode: str
          column_headers: dict of strings, maps the column letters (A, B, C) to
                          the desired name which will be viewable in the
                          worksheet.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
        """
        data = gdata.spreadsheets.data.Data(
            insertion_mode=insertion_mode, num_rows=str(num_rows),
            start_row=str(start_row))
        for index, name in column_headers.iteritems():
            data.column.append(gdata.spreadsheets.data.Column(
                index=index, name=name))
        new_table = gdata.spreadsheets.data.Table(
            title=atom.data.Title(text=title), summary=atom.data.Summary(summary),
            worksheet=gdata.spreadsheets.data.Worksheet(name=worksheet_name),
            header=gdata.spreadsheets.data.Header(row=str(header_row)), data=data)
        return self.post(new_table, TABLES_URL % spreadsheet_key,
                         auth_token=auth_token, **kwargs)

    AddTable = add_table

    def get_tables(self, spreadsheet_key,
                   desired_class=gdata.spreadsheets.data.TablesFeed,
                   auth_token=None, **kwargs):
        """Retrieves a feed listing the tables in this spreadsheet.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          desired_class: class descended from atom.core.XmlElement to which a
                         successful response should be converted. If there is no
                         converter function specified (converter=None) then the
                         desired_class will be used in calling the
                         atom.core.parse function. If neither
                         the desired_class nor the converter is specified, an
                         HTTP reponse object will be returned. Defaults to
                         gdata.spreadsheets.data.TablesFeed.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
        """
        return self.get_feed(TABLES_URL % spreadsheet_key,
                             desired_class=desired_class, auth_token=auth_token,
                             **kwargs)

    GetTables = get_tables

    def add_record(self, spreadsheet_key, table_id, fields,
                   title=None, auth_token=None, **kwargs):
        """Adds a new row to the table.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          table_id: str, The ID of the table within the worksheet which should
                    receive this new record. The table ID can be found using the
                    get_table_id method of a gdata.spreadsheets.data.Table.
          fields: dict of strings mapping column names to values.
          title: str, optional The title for this row.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
        """
        new_record = gdata.spreadsheets.data.Record()
        if title is not None:
            new_record.title = atom.data.Title(text=title)
        for name, value in fields.iteritems():
            new_record.field.append(gdata.spreadsheets.data.Field(
                name=name, text=value))
        return self.post(new_record, RECORDS_URL % (spreadsheet_key, table_id),
                         auth_token=auth_token, **kwargs)

    AddRecord = add_record

    def get_records(self, spreadsheet_key, table_id,
                    desired_class=gdata.spreadsheets.data.RecordsFeed,
                    auth_token=None, **kwargs):
        """Retrieves the records in a table.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          table_id: str, The ID of the table within the worksheet whose records
                    we would like to fetch. The table ID can be found using the
                    get_table_id method of a gdata.spreadsheets.data.Table.
          desired_class: class descended from atom.core.XmlElement to which a
                         successful response should be converted. If there is no
                         converter function specified (converter=None) then the
                         desired_class will be used in calling the
                         atom.core.parse function. If neither
                         the desired_class nor the converter is specified, an
                         HTTP reponse object will be returned. Defaults to
                         gdata.spreadsheets.data.RecordsFeed.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
        """
        return self.get_feed(RECORDS_URL % (spreadsheet_key, table_id),
                             desired_class=desired_class, auth_token=auth_token,
                             **kwargs)

    GetRecords = get_records

    def get_record(self, spreadsheet_key, table_id, record_id,
                   desired_class=gdata.spreadsheets.data.Record,
                   auth_token=None, **kwargs):
        """Retrieves a single record from the table.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          table_id: str, The ID of the table within the worksheet whose records
                    we would like to fetch. The table ID can be found using the
                    get_table_id method of a gdata.spreadsheets.data.Table.
          record_id: str, The ID of the record within this table which we want to
                     fetch. You can find the record ID using get_record_id() on
                     an instance of the gdata.spreadsheets.data.Record class.
          desired_class: class descended from atom.core.XmlElement to which a
                         successful response should be converted. If there is no
                         converter function specified (converter=None) then the
                         desired_class will be used in calling the
                         atom.core.parse function. If neither
                         the desired_class nor the converter is specified, an
                         HTTP reponse object will be returned. Defaults to
                         gdata.spreadsheets.data.RecordsFeed.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
        """
        return self.get_entry(RECORD_URL % (spreadsheet_key, table_id, record_id),
                              desired_class=desired_class, auth_token=auth_token,
                              **kwargs)

    GetRecord = get_record

    def get_cells(self, spreadsheet_key, worksheet_id,
                  desired_class=gdata.spreadsheets.data.CellsFeed,
                  auth_token=None, **kwargs):
        """Retrieves the cells which have values in this spreadsheet.

        Blank cells are not included.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          worksheet_id: str, The unique ID of the worksheet in this spreadsheet
                        whose cells we want. This can be obtained using
                        WorksheetEntry's get_worksheet_id method.
          desired_class: class descended from atom.core.XmlElement to which a
                         successful response should be converted. If there is no
                         converter function specified (converter=None) then the
                         desired_class will be used in calling the
                         atom.core.parse function. If neither
                         the desired_class nor the converter is specified, an
                         HTTP reponse object will be returned. Defaults to
                         gdata.spreadsheets.data.CellsFeed.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
        """
        return self.get_feed(CELLS_URL % (spreadsheet_key, worksheet_id),
                             auth_token=auth_token, desired_class=desired_class,
                             **kwargs)

    GetCells = get_cells

    def get_cell(self, spreadsheet_key, worksheet_id, row_num, col_num,
                 desired_class=gdata.spreadsheets.data.CellEntry,
                 auth_token=None, **kwargs):
        """Retrieves a single cell from the worksheet.

        Indexes are 1 based so the first cell in the worksheet is 1, 1.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          worksheet_id: str, The unique ID of the worksheet in this spreadsheet
                        whose cells we want. This can be obtained using
                        WorksheetEntry's get_worksheet_id method.
          row_num: int, The row of the cell that we want. Numbering starts with 1.
          col_num: int, The column of the cell we want. Numbering starts with 1.
          desired_class: class descended from atom.core.XmlElement to which a
                         successful response should be converted. If there is no
                         converter function specified (converter=None) then the
                         desired_class will be used in calling the
                         atom.core.parse function. If neither
                         the desired_class nor the converter is specified, an
                         HTTP reponse object will be returned. Defaults to
                         gdata.spreadsheets.data.CellEntry.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
        """
        return self.get_entry(
            CELL_URL % (spreadsheet_key, worksheet_id, row_num, col_num),
            auth_token=auth_token, desired_class=desired_class, **kwargs)

    GetCell = get_cell

    def get_list_feed(self, spreadsheet_key, worksheet_id,
                      desired_class=gdata.spreadsheets.data.ListsFeed,
                      auth_token=None, **kwargs):
        """Retrieves the value rows from the worksheet's list feed.

        The list feed is a view of the spreadsheet in which the first row is used
        for column names and subsequent rows up to the first blank line are
        records.

        Args:
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          worksheet_id: str, The unique ID of the worksheet in this spreadsheet
                        whose cells we want. This can be obtained using
                        WorksheetEntry's get_worksheet_id method.
          desired_class: class descended from atom.core.XmlElement to which a
                         successful response should be converted. If there is no
                         converter function specified (converter=None) then the
                         desired_class will be used in calling the
                         atom.core.parse function. If neither
                         the desired_class nor the converter is specified, an
                         HTTP reponse object will be returned. Defaults to
                         gdata.spreadsheets.data.ListsFeed.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
        """
        return self.get_feed(LISTS_URL % (spreadsheet_key, worksheet_id),
                             auth_token=auth_token, desired_class=desired_class,
                             **kwargs)

    GetListFeed = get_list_feed

    def add_list_entry(self, list_entry, spreadsheet_key, worksheet_id,
                       auth_token=None, **kwargs):
        """Adds a new row to the worksheet's list feed.

        Args:
          list_entry: gdata.spreadsheets.data.ListsEntry An entry which contains
                      the values which should be set for the columns in this
                      record.
          spreadsheet_key: str, The unique ID of this containing spreadsheet. This
                           can be the ID from the URL or as provided in a
                           Spreadsheet entry.
          worksheet_id: str, The unique ID of the worksheet in this spreadsheet
                        whose cells we want. This can be obtained using
                        WorksheetEntry's get_worksheet_id method.
          auth_token: An object which sets the Authorization HTTP header in its
                      modify_request method. Recommended classes include
                      gdata.gauth.ClientLoginToken and gdata.gauth.AuthSubToken
                      among others. Represents the current user. Defaults to None
                      and if None, this method will look for a value in the
                      auth_token member of SpreadsheetsClient.
        """
        return self.post(list_entry, LISTS_URL % (spreadsheet_key, worksheet_id),
                         auth_token=auth_token, **kwargs)

    AddListEntry = add_list_entry


class SpreadsheetQuery(gdata.client.Query):

    def __init__(self, title=None, title_exact=None, **kwargs):
        """Adds Spreadsheets feed query parameters to a request.

        Args:
          title: str Specifies the search terms for the title of a document.
                 This parameter used without title-exact will only submit partial
                 queries, not exact queries.
          title_exact: str Specifies whether the title query should be taken as an
                       exact string. Meaningless without title. Possible values are
                       'true' and 'false'.
        """
        gdata.client.Query.__init__(self, **kwargs)
        self.title = title
        self.title_exact = title_exact

    def modify_request(self, http_request):
        gdata.client._add_query_param('title', self.title, http_request)
        gdata.client._add_query_param('title-exact', self.title_exact,
                                      http_request)
        gdata.client.Query.modify_request(self, http_request)

    ModifyRequest = modify_request


class WorksheetQuery(SpreadsheetQuery):
    pass


class ListQuery(gdata.client.Query):

    def __init__(self, order_by=None, reverse=None, sq=None, **kwargs):
        """Adds List-feed specific query parameters to a request.

        Args:
          order_by: str Specifies what column to use in ordering the entries in
                    the feed. By position (the default): 'position' returns
                    rows in the order in which they appear in the GUI. Row 1, then
                    row 2, then row 3, and so on. By column:
                    'column:columnName' sorts rows in ascending order based on the
                    values in the column with the given columnName, where
                    columnName is the value in the header row for that column.
          reverse: str Specifies whether to sort in descending or ascending order.
                   Reverses default sort order: 'true' results in a descending
                   sort; 'false' (the default) results in an ascending sort.
          sq: str Structured query on the full text in the worksheet.
              [columnName][binaryOperator][value]
              Supported binaryOperators are:
              - (), for overriding order of operations
              - = or ==, for strict equality
              - <> or !=, for strict inequality
              - and or &&, for boolean and
              - or or ||, for boolean or
        """
        gdata.client.Query.__init__(self, **kwargs)
        self.order_by = order_by
        self.reverse = reverse
        self.sq = sq

    def modify_request(self, http_request):
        gdata.client._add_query_param('orderby', self.order_by, http_request)
        gdata.client._add_query_param('reverse', self.reverse, http_request)
        gdata.client._add_query_param('sq', self.sq, http_request)
        gdata.client.Query.modify_request(self, http_request)

    ModifyRequest = modify_request


class TableQuery(ListQuery):
    pass


class CellQuery(gdata.client.Query):

    def __init__(self, min_row=None, max_row=None, min_col=None, max_col=None,
                 range=None, return_empty=None, **kwargs):
        """Adds Cells-feed specific query parameters to a request.

        Args:
          min_row: str or int Positional number of minimum row returned in query.
          max_row: str or int Positional number of maximum row returned in query.
          min_col: str or int Positional number of minimum column returned in query.
          max_col: str or int Positional number of maximum column returned in query.
          range: str A single cell or a range of cells. Use standard spreadsheet
                 cell-range notations, using a colon to separate start and end of
                 range. Examples:
                 - 'A1' and 'R1C1' both specify only cell A1.
                 - 'D1:F3' and 'R1C4:R3C6' both specify the rectangle of cells with
                   corners at D1 and F3.
          return_empty: str If 'true' then empty cells will be returned in the feed.
                        If omitted, the default is 'false'.
        """
        gdata.client.Query.__init__(self, **kwargs)
        self.min_row = min_row
        self.max_row = max_row
        self.min_col = min_col
        self.max_col = max_col
        self.range = range
        self.return_empty = return_empty

    def modify_request(self, http_request):
        gdata.client._add_query_param('min-row', self.min_row, http_request)
        gdata.client._add_query_param('max-row', self.max_row, http_request)
        gdata.client._add_query_param('min-col', self.min_col, http_request)
        gdata.client._add_query_param('max-col', self.max_col, http_request)
        gdata.client._add_query_param('range', self.range, http_request)
        gdata.client._add_query_param('return-empty', self.return_empty,
                                      http_request)
        gdata.client.Query.modify_request(self, http_request)

    ModifyRequest = modify_request
